/*
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
package utopiaintelpackage.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import main.CommonMethods;
import main.Main;

import org.apache.log4j.Logger;

import utopiaintelpackage.survey.Survey;
import database.DBConnectionFactory;

/**
 * A class that saves and retreives Survey objects
 * 
 * 
 */
public class SurveytoDB {
   /**
    * If a province doesn't exist yet, it needs be added
    * 
    * @param som
    */
   private static void addProvince(final Survey survey) {
	  PreparedStatement ps = null;
	  try {
		 if (!CommonMethods.kdExists(survey.getKdloc())) {
			CommonMethods.addDefaultKD(survey.getKdloc());
		 }

		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("INSERT INTO province VALUES (?,?,?,?,?,?,?,?,?,false,?)");
		 ps.setString(1, survey.getName());
		 ps.setString(2, "");
		 ps.setString(3, "");
		 ps.setString(4, "");
		 ps.setString(5, "");
		 ps.setString(6, "");
		 ps.setInt(7, 0);
		 ps.setInt(8, 0);
		 ps.setInt(9, 0);
		 ps.setTimestamp(10, new Timestamp(survey.getUpdated().getTime()));
		 ps.executeUpdate();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
   }

   public static Survey getSurvey(final String name) {
	  Survey survey = null;
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM " + "survey,province WHERE survey.name = ? AND survey.name = province.name");
		 ps.setString(1, name);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			survey = new Survey();
			survey.setName(rs.getString("name"));
			String[] split = rs.getString("buildings").split("\n");
			for (final String s : split) {
			   final String[] tabsplit = s.split("\t");
			   survey.setBuilding(tabsplit[0], Integer.parseInt(tabsplit[1]));
			}
			split = rs.getString("inprogress").split("\n");
			for (final String s : split) {
			   final String[] tabsplit = s.split("\t");
			   survey.setBuildingInProgress(tabsplit[0], Integer.parseInt(tabsplit[1]));
			}
			survey.setUpdated(new Date(rs.getTimestamp("updated").getTime()));
			survey.setExportLine(rs.getString("export"));
			survey.setSavedBy(rs.getString("savedby"));
			survey.setRace(rs.getString("race"));
			survey.setPersonality(rs.getString("personality"));
			survey.setLand(rs.getInt("land"));
			survey.setSelfSurvey(survey.getKdloc().equals("(" + Main.getBotSettings().getKdLoc() + ")"));
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return survey;
   }

   public static String saveSurvey(final Survey survey) {
	  if ("".equals(CommonMethods.provinceExists(survey.getName()))) {
		 addProvince(survey);
	  }
	  List<String> buildingList = CommonMethods.listBuildings();
	  String buildings = "";
	  for (final String s : buildingList) {
		 buildings += s + "\t" + survey.getBuilding(s) + "\n";
	  }
	  String inprogress = "";
	  for (final String s : buildingList) {
		 inprogress += s + "\t" + survey.getBuildingInProgress(s) + "\n";
	  }
	  PreparedStatement ps = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("DELETE FROM survey WHERE name = ?");
		 ps.setString(1, survey.getName());
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e) {
		 }

		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("INSERT INTO survey VALUES (?,?,?,?,?,?)");
		 ps.setString(1, survey.getName());
		 ps.setString(2, buildings);
		 ps.setString(3, inprogress);
		 ps.setTimestamp(4, new Timestamp(Calendar.getInstance().getTimeInMillis()));
		 ps.setString(5, survey.getExportLine());
		 ps.setString(6, survey.getSavedBy());
		 ps.executeUpdate();

		 out = "Survey added";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }
}
